const ExcelJS = require('exceljs');
const fs = require('fs');
const moment = require('moment');

const {toExcel} = require('./exportExcel')

const db_order = {
  "status": 1,
  "products": [
    {
      "colors": [
        "咖啡色",
        "金属灰"
      ],
      "serieId": "66c94b419df01a698ce1139a",
      "width": "4000",
      "height": "5000",
      "serieInfo": {
        "colors": [
          "咖啡色",
          "金属灰"
        ],
        "_id": "66c94b419df01a698ce1139a",
        "name": "W易拆洗二趟纱网",
        "category": 1
      },
      "projects": [
        {
          "colors": [],
          "discount": 1,
          "name": "面积计价",
          "price": 210,
          "quantity": 25,
          "unitName": "平方",
          "size": "5000*5000"
        },
        {
          "colors": [],
          "discount": 1,
          "name": "5白+20A+5白",
          "price": 30,
          "quantity": "10",
          "unitName": "平方",
          "size": "5000*5000"
        }
      ],
      "id": "66d6bdfae138b091a50332dc_0"
    },
    {
      "colors": [
        "咖啡色",
        "金属灰"
      ],
      "serieId": "66c94b419df01a698ce1139a",
      "width": "4000",
      "height": "5000",
      "serieInfo": {
        "colors": [
          "咖啡色",
          "金属灰"
        ],
        "_id": "66c94b419df01a698ce1139a",
        "name": "W易拆洗二趟纱网",
        "category": 1
      },
      "projects": [
        {
          "colors": [],
          "discount": 1,
          "name": "面积计价",
          "price": 210,
          "quantity": 25,
          "unitName": "平方",
          "size": "5000*5000"
        },
        {
          "colors": [],
          "discount": 1,
          "name": "5白+20A+5白",
          "price": 30,
          "quantity": "10",
          "unitName": "平方",
          "size": "5000*5000"
        }
      ],
      "id": "66e00607e138b091a50332e4_1"
    },
    {
      "colors": [
        "黄檀木",
        "金拉丝"
      ],
      "serieId": "66c9994125a37147e47fbb31",
      "position": "次卧",
      "width": "4000",
      "height": "4000",
      "serieInfo": {
        "colors": [
          "黄檀木",
          "金拉丝",
          "银拉丝"
        ],
        "_id": "66c9994125a37147e47fbb31",
        "name": "护墙板-肤感木纹",
        "category": 2
      },
      "projects": [
        {
          "colors": [],
          "discount": 1,
          "name": "18mm实木多层板",
          "price": 138,
          "quantity": "10",
          "unitName": "平方",
          "size": "4000*4000"
        },
        {
          "colors": [],
          "discount": 1,
          "name": "10白+18A+10白",
          "price": 280,
          "quantity": "5",
          "unitName": "平方",
          "size": "4000*4000"
        },
        {
          "size": "2000*2000",
          "discount": 1,
          "name": "L28一体拉手",
          "unitName": "扇",
          "price": 80,
          "quantity": "2"
        },
        {
          "size": "2000*2000",
          "discount": 1,
          "name": "柜门拉直器",
          "unitName": "条",
          "price": 80,
          "quantity": "4"
        },
        {
          "colors": [],
          "discount": 1,
          "name": "中线开槽加工",
          "price": 30,
          "quantity": "8",
          "unitName": "米",
          "size": "2000*2000"
        }
      ],
      "id": "66e00607e138b091a50332e5_2"
    }
  ],
  "_id": "66d6bdfae138b091a50332dd",
  "storeId": {
    "status": 1,
    "area": [],
    "_id": "66d279aaddd2b1f1110ff41b",
    "name": "铂润家居-云南陆良李雪梅",
    "shortName": "陆良",
    "createTime": "2024-08-31T02:02:18.849Z",
    "updateTime": "2024-09-02T05:39:50.665Z",
    "__v": 0,
    "deposit_ratio": 0.3
  },
  "customId": {
    "_id": "66b43baf814cce14211b00e6",
    "name": "彭亮",
    "phone": "",
    "address": "南岸区海棠晓月B2-3栋3单元3-3",
    "createTime": "2024-08-08T03:29:51.706Z",
    "updateTime": "2024-08-24T05:48:33.250Z",
    "__v": 0
  },
  "totalAmount": 14600,
  "deposit": 4380,
  "depositRemark": "请打30%定金下单生产：4380元",
  "create_user": {
    "password": "$2b$10$L47ryDI2lDqCkDuMBeEfSeFUPA5DRDrOTV7oe/aVnzt3.haSPM78a",
    "status": 1,
    "_id": "6699cd2d09877031c40031b1",
    "phone": "18223070484",
    "userName": "hxy",
    "type": 1,
    "createTime": "2024-07-19T02:19:25.162Z",
    "updateTime": "2024-07-19T02:19:25.162Z",
    "__v": 0,
    "name": "胡小燕"
  },
  "createTime": "2024-09-03T07:42:50.638Z",
  "updateTime": "2024-09-11T08:07:27.299Z",
  "__v": 0
}

async function createStyledExcel(orderLists) {
    // 创建一个新的工作簿
    let workbook = new ExcelJS.Workbook();
   
    // 添加一个工作表
    let worksheet = workbook.addWorksheet(`表1`);

    for(let i=0;i<orderLists.length;i++){
      const orderItem = orderLists[i]
      
      // 添加表头
      // worksheet.columns = [
      //   { header: '序号', key: 'xh', width: 4 },
      //   { header: '安装位置', key: 'azwz', width: 10 },
      //   { header: '产品型号', key: 'cpxh', width: 50 },
      //   { header: '颜色', key: 'ys', width: 16 },
      //   { header: '宽*高*墙厚', key: 'kgh', width: 15 },
      //   { header: '明细项目', key: 'mxxm', width: 50 },
      //   { header: '数量', key: 'sl', width: 5 },
      //   { header: '单位', key: 'dw', width: 8 },
      //   { header: '单价', key: 'dj', width: 5 },
      //   { header: '折后单价', key: 'zhdj', width: 8 },
      //   { header: '出厂价', key: 'ccj', width: 10 },
      //   { header: '折扣', key: 'zk', width: 6 },
      //   { header: '折后价', key: 'zhj', width: 10 },
      //   { header: '合计', key: 'hj', width: 10 },
      // ];
      // 设置第一行的字体样式（标题行）
      //worksheet.getRow(1).font = {bold: true,}

      // 添加标题
      const titleRow = worksheet.addRow(['产品定制报价清单'])
      titleRow.font

      // 添加表头
      const headerRow =  worksheet.addRow(['序号','安装位置','产品型号','颜色','宽*高*墙厚','明细项目','数量','单位','单价','折后单价','出厂价','折扣','折后价','合计',])
      // 设置表头样式
      headerRow.font = { 
        size: 12,
        bold: true,
        color: { argb: 'FF0000' }, // 红色
        fontName: 'Arial'
      };

      // 设置列宽度
      worksheet.getColumn('A').width = 5 // 设置第一列的宽度为 5（字符单位）
      worksheet.getColumn('B').width = 10
      worksheet.getColumn('C').width = 50
      worksheet.getColumn('D').width = 16
      worksheet.getColumn('E').width = 15
      worksheet.getColumn('F').width = 50
      worksheet.getColumn('G').width = 5
      worksheet.getColumn('H').width = 8
      worksheet.getColumn('I').width = 5
      worksheet.getColumn('J').width = 8
      worksheet.getColumn('K').width = 10
      worksheet.getColumn('L').width = 6
      worksheet.getColumn('M').width = 10
      worksheet.getColumn('N').width = 10
      //worksheet.getColumn(1).font = {color: { argb: '0e75e3' } } // 设置第一列样式

      // 添加数据的行数（即明细项目的条数）
      let projectNum = 0
      orderItem.products.forEach((item,index)=>{
        item.projects.forEach((item2,index2) =>{
          projectNum++
          worksheet.addRow([  // 向表中添加数据
            index+1, 
            item.position || '', 
            item.serieInfo.name,
            item.colors.join('，'),
            `${item2.size}`,
            `${item2.name}`,
            `${item2.quantity}`,
            `${item2.unitName}`,
            `${item2.price}`,
            `${Math.round(item2.price * item2.discount)}`,
            `${Math.round(item2.price * item2.quantity)}`,
            `${item2.discount<1?item2.discount:''}`,
            `${Math.round(item2.price * item2.discount * item2.quantity)}`,
            `${getTotal(item.projects)}`,
          ]);
        })
        // let startIndex = 1 + (index>0?projectNum-item.projects.length+1:1)
        // mergeSimilarCells(worksheet, 'C',startIndex,projectNum);
      
      })
    }
    // 合并单元格
    mergeSimilarCells(worksheet,['A','B','C','D','E','N'],0,1000);

    // 写入文件
    await workbook.xlsx.writeFile(`${moment().format('YYYYMMDD')}.xlsx`);
}

function getTotal (lists){ // 获取系列下的明显项目合计
  if(!lists) return ''
  const total = lists.reduce((accumulator, item) => accumulator + (item.price*item.discount*item.quantity), 0);
  return Math.round(total)
}


/**
   * 纵向合并某列相同值的单元格
   * @param {*} worksheet 工作表对象
   * @param {*} column 合并得列：ABCD....
   * @param {*} startRowIndex 计算开始索引值（开始行），不是合并开始行
   * @param {*} endRowIndex 计算结束索引值（结束行），不是合并结束行
   */
function mergeSimilarCells(worksheet,columnArr,startRowIndex,endRowIndex,font) {
  let curColumn='' // 当前列
  let temvar={}  // 临时变量:{A:{},B:{},E:{}}
  // 循环定义变量，下文保存数据用
  columnArr.forEach(item =>{
    temvar[item] = {}
  })

  let mergeCeilsArr = []  // 对象数组，保存需要合并的单元格信息：如{column,startIndex,endIndex}
  worksheet.eachRow({ includeEmpty: true }, function(row, rowNumber) { 
    if(rowNumber>=startRowIndex && rowNumber<=endRowIndex){
      for(let i=0;i<columnArr.length;i++){
        curColumn = columnArr[i]
        // 获取单元格值
        const curCellvalue =  row.getCell(curColumn).value
        //console.log(`curCellvalue:${curCellvalue}`) 
        if(curCellvalue == temvar[curColumn].preCellvalue && temvar[curColumn].serialNumber==row.getCell('A').value){
          temvar[curColumn].mergeEndIndex++
        }else{
          if(temvar[curColumn].mergeStartIndex && temvar[curColumn].mergeEndIndex && temvar[curColumn].mergeEndIndex>temvar[curColumn].mergeStartIndex){
            mergeCeilsArr.push({
              column:curColumn,
              mergeStartIndex:temvar[curColumn].mergeStartIndex,
              mergeEndIndex:temvar[curColumn].mergeEndIndex
            })  // 保存需要合并单元格
          }
          temvar[curColumn].preCellvalue = curCellvalue
          temvar[curColumn].mergeStartIndex = rowNumber
          temvar[curColumn].mergeEndIndex = rowNumber
          temvar[curColumn].serialNumber = row.getCell('A').value
        }
        //console.log(temvar[curColumn])
      }
      
    } 
  });
  
  for (const key in temvar) {
    if(temvar[key].mergeStartIndex && temvar[key].mergeEndIndex && temvar[key].mergeEndIndex>temvar[key].mergeStartIndex){
      mergeCeilsArr.push({
        column:key,
        mergeStartIndex:temvar[key].mergeStartIndex,
        mergeEndIndex:temvar[key].mergeEndIndex
      })  // 保存需要合并单元格
    }
  }
  
  //console.log(mergeCeilsArr)

  // 开始合并
  for(let j=0;j<mergeCeilsArr.length;j++){
    const item = mergeCeilsArr[j]
    //worksheet.mergeCells('A1:C1');
    worksheet.mergeCells(`${item.column}${item.mergeStartIndex}:${item.column}${item.mergeEndIndex}`);
    // 获取合并后的单元格
    const mergedCell = worksheet.getCell(`${item.column}${item.mergeStartIndex}`);
    // 设置对齐方式
    mergedCell.alignment = { vertical: 'middle', horizontal: 'center' };
    font && (mergedCell.font = font)
  }
}
   
  createStyledExcel([db_order]);


